MySQL 8.0新特性 |
您所在的位置：网站首页 › mysql meb备份 › MySQL 8.0新特性 |
|
前言 在 MySQL 8.0中，引入了一个轻量级的备份锁Backup Lock，这个锁可以保证备份一致性，而且阻塞的操作相对比较少，是一个非常重要的特性 FTWRL在之前无论是物理备份 还是逻辑备份，需要执行flush table with read lock，简称 FTWRL，以获取一致性状态； FTWRL 会关闭所有打开的表，并加上一把全局读锁，因此这个命令杀伤性非常大，容易造成各种阻塞。 下面场景，FTWRL 被慢查询或 大事务 或 大表DDL 阻塞，后续 select 和 DML 又被 FTWRL 阻塞，最终甚至导致实例崩溃。 # session 1 mysql> flush table qiqios.pxb1 with read lock; # 这个测试表全局读锁是基于会话的，需要在对应会话解锁 # session 2 mysql> alter table qiqios.pxb1 add column b int; # session 3 和 4 mysql> insert into qiqios.pxb1 values(1,1); mysql> insert into qiqios.pxb1 values(2,2); # session 1 查看 mysql> select * from information_schema.processlist where user='root' and command 'sleep'; +------+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+ ID USER HOST DB COMMAND TIME STATE INFO +------+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+ 2040 root localhost NULL Query 0 executing select * from information_schema.processlist where user='root' and command 'sleep' 2044 root localhost NULL Query 209 Waiting for table metadata lock alter table qiqios.pxb1 add column b int 2045 root localhost NULL Query 41 Waiting for table metadata lock insert into qiqios.pxb1 values(1,1) 2046 root localhost NULL Query 3 Waiting for table metadata lock insert into qiqios.pxb1 values(2,2) +------+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+就算把备份放在从库执行，同样也不能完全避免；如果从库需要同步大事务 或 分担读压力，就有可能会阻塞 FTWRL, 进而再阻塞后续操作；另外，MTS 和 FTWRL 的死锁 bug，也可能会导致类似问题。 因此，总的来说， FTWRL 还是一个重量级锁，因为其即会被读写阻塞，又会阻塞读写，容易造成各种阻塞甚至实例崩溃，因此MySQL 8.0 提供新的解决方案 Backup Lock Backup Lock官方文档说明：https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html 在 MySQL 8.0中，未了解决备份 FTWRL 的问题，引入了轻量级的备份锁； 可以通过 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE , 以获取和释放备份锁，执行该语句需要 BACKUP_ADMIN 权限。 多个会话可以同时持有备份锁，UNLOCK INSTANCE 释放当前会话持有的备份锁，必须要在加锁的会话中解锁或KILL，如果会话终止，也会释放会话所持有的备份锁 backup lock 不会阻塞读写操作 # session 1 mysql> lock instance for backup; Query OK, 0 rows affected (0.00 sec) # session 2 mysql> select * from qiqios.pxb1; +------+------+------+ id a b +------+------+------+ 1 1 NULL +------+------+------+ # session 3 mysql> insert into qiqios.pxb1 values(2,2,2); Query OK, 1 row affected (0.01 sec) mysql> select * from information_schema.processlist where user='root' and command 'sleep'; +------+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------------------------+ ID USER HOST DB COMMAND TIME STATE INFO +------+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------------------------+ 2045 root localhost NULL Query 0 executing select * from information_schema.processlist where user='root' and command 'sleep' +------+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------------------------+ # 这边测试表是小表，所以执行 DML 语句完成速度很快，processlist 进程看不到backup lock 会阻塞大部分 DDL 操作，包括创建/删除表、加/减字段、增/删索引、optimze/analyze/repair table 等 注意：在加备份锁的会话中是不会阻塞 DDL操作 # session 1 mysql> lock instance for backup; ysql> create table qiqios.pxb4 (a int); Query OK, 0 rows affected (0.05 sec) mysql> alter table qiqios.pxb4 add column e int; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop table qiqios.pxb4; Query OK, 0 rows affected (0.04 sec) # session 2 mysql> create table qiqios.pxb2 like qiqios.pxb1; # session 3 mysql> show tables from qiqios; +------------------+ Tables_in_qiqios +------------------+ pxb1 pxb2 +------------------+ 2 rows in set (0.00 sec) mysql> drop table qiqios.pxb2; # session 4 mysql> select * from information_schema.processlist where user='root' and command 'sleep' and state 'executing'; +------+------+-----------+------+---------+------+-------------------------+-------------------------------------------+ ID USER HOST DB COMMAND TIME STATE INFO +------+------+-----------+------+---------+------+-------------------------+-------------------------------------------+ 2044 root localhost NULL Query 1034 Waiting for backup lock create table qiqios.pxb2 like qiqios.pxb1 2046 root localhost NULL Query 113 Waiting for backup lock drop table qiqios.pxb2 +------+------+-----------+------+---------+------+-------------------------+-------------------------------------------+ 2 rows in set (0.00 sec)总的来说，备份锁还是非常实用的，毕竟其不会影响业务的正常读写；至于备份锁和 DDL 操作的冲突，还是有很多方法可以避免，比如错开备份和变更的时间，通过 pt-online-schema-change/gh-ost 避免长时间阻塞等等。 随着备份锁的引入，oracle 官方备份工具 MEB 8.0 个 Percona 开源备份工具 XtraBackup 8.0，也是更新了对 backup lock 的支持，参考文档如下： MEB 8.0：https://docs.oracle.com/cd/E17952_01/mysql-enterprise-backup-8.0-en/mysqlbackup.backup.html XtraBackup 8.0：https://www.percona.com/doc/percona-server/8.0/management/backup_locks.html |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |